package com.sunwayworld.dev.core.db.dialect;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.sunwayworld.dev.util.BeanUtils;
import com.sunwayworld.dev.core.db.ColumnModel;
import com.sunwayworld.dev.core.db.PrimaryKeyModel;
import com.sunwayworld.dev.core.db.TableModel;
import com.sunwayworld.dev.core.valueobject.Field;
import com.sunwayworld.dev.util.StringDevUtil;

class SQLServerTableEngine implements TableEngine {

	private Connection conn = null;

	private String catalog;

	private String schema;

	private String tableName;

	public SQLServerTableEngine(String tableName, Connection conn) {
		this.tableName = tableName;
		this.conn = conn;

	}

	public SQLServerTableEngine(Connection conn) {
		this.conn = conn;
	}

	public ColumnModel[] generateColumnModel() {
		ColumnModel[] columnModels = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			StringBuffer sqlStr = new StringBuffer();

			sqlStr.append("\n select a.name table_name,b.name column_name");
			sqlStr.append("\n ,b.length data_length,c.value comments");
			sqlStr.append("\n ,d.name data_type,d.scale data_scale");
			sqlStr.append("\n ,d.prec data_precision,b.isnullable nullable");
			sqlStr.append("\n ,e.text data_default");
			sqlStr
					.append("\n from sysobjects a join syscolumns b on a.id=b.id ");
			sqlStr.append("\n inner join systypes d on b.xtype=d.xusertype");
			sqlStr
					.append("\n left join  sysProperties c  on b.id=c.id and  b.colid=c.smallid");
			sqlStr.append("\n left join syscomments e on b.cdefault=e.id");

			sqlStr.append("\n where a.name = '" + tableName + "'");

		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sqlStr.toString());

			int num = 0;

			if (rs.last()) {

				num = rs.getRow();

			} else {
				num = 0;
			}
			rs.beforeFirst();

			columnModels = new ColumnModel[num];
			int i = 0;
			while (rs.next()) {
				columnModels[i] = new ColumnModel();
				BeanUtils.copyProperties(rs, columnModels[i]);

				// System.out.println("private String
				// "+columnModels[i].getColumn_name()+";\n");
				i++;

			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {

			cleanup(stmt, rs);
		}
		return columnModels;
	}

	/**
	 * 得到指定表的信息
	 * 
	 * @return TableModel.class
	 */
	public TableModel generateTableModel() {
		ResultSet rs = null;
		Statement stmt = null;
		TableModel tableModel = new TableModel();
		try {
			// String sqlStr = "select * from user_tab_comments where table_name
			// = '"
			// + tableName + "'";
			//
			// stmt = conn.createStatement();
			//
			// rs = stmt.executeQuery(sqlStr);
			// if (rs.next()) {
			// BeanUtils.copyProperties(rs, tableModel);
			// }
			tableModel.setPkModel(this.generatePKModel());
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			cleanup(stmt, rs);
		}
		return tableModel;
	}

	/**
	 * 得到指定表的主键信息
	 * 
	 * @return PrimaryKeyModel.class
	 */
	public PrimaryKeyModel[] generatePKModel() {
		PrimaryKeyModel[] pkModels = null;
		ResultSet rs = null;
		try {
			DatabaseMetaData dmeta = conn.getMetaData();
			// System.out.print(tableName);

			rs = dmeta.getPrimaryKeys(catalog, schema, tableName);
			ArrayList<PrimaryKeyModel> alist = new ArrayList<PrimaryKeyModel>();

			while (rs.next()) {

				PrimaryKeyModel pkModel = new PrimaryKeyModel();
				BeanUtils.copyProperties(rs, pkModel);
				alist.add(pkModel);
			}
			pkModels = new PrimaryKeyModel[alist.size()];
			for (int i = 0; i < alist.size(); i++) {
				pkModels[i] = (PrimaryKeyModel) alist.get(i);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			cleanup(null, rs);
		}
		return pkModels;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	private void cleanup(PreparedStatement pstmt, ResultSet rs) {
		try {
			if (pstmt != null)
				pstmt.close();
			pstmt = null;
		} catch (Exception e) {

		}
		try {
			if (rs != null)
				rs.close();
			rs = null;
		} catch (Exception ex) {

		}

	}

	private void cleanup(Statement stmt, ResultSet rs) {
		try {
			if (stmt != null)
				stmt.close();
			stmt = null;
		} catch (Exception e) {

		}
		try {
			if (rs != null)
				rs.close();
			rs = null;
		} catch (Exception ex) {

		}

	}

	// /////////////////////////////////////

	public Field[] getFieldsFromDB() {

		ColumnModel[] colModel = this.generateColumnModel();

		if (colModel == null) {
			return null;
		}

		Field[] fields = new Field[colModel.length];
		for (int i = 0, n = colModel.length; i < n; i++) {
			fields[i] = new Field();

			fields[i].setName(StringDevUtil.toLowerCase(colModel[i]
					.getColumn_name()));

			fields[i].setNotation(StringDevUtil.toString(colModel[i]
					.getComments()));
			if("".equals(fields[i].getNotation())){
				fields[i].setNotation(fields[i].getName());
			}
			if ("N".equals(colModel[i].getNullable())) {
				fields[i].setIsNull("true");
			} else {
				fields[i].setIsNull("false");
			}

			String oracle_type = StringDevUtil.toString(colModel[i]
					.getData_type());

			// //////////////////////////处理sqlserver 数据库 和java 中的类型匹配
			if ("VARCHAR".equalsIgnoreCase(oracle_type)) {
				fields[i].setType("String");
			} else if ("DATE".equals(oracle_type)) {
				fields[i].setType("java.util.Date");
			} else if ("NUMBER".equalsIgnoreCase(oracle_type)) {
				if ("0".equals(colModel[i].getData_Scale())) {
					fields[i].setType("Long");
				} else {

					fields[i].setType("java.math.BigDecimal");
				}

			}else if ("INT".equalsIgnoreCase(oracle_type)) {
				if ("0".equals(colModel[i].getData_Scale())) {
					fields[i].setType("Long");
				} else {

					fields[i].setType("java.math.BigDecimal");
				}
			}else {
				fields[i].setType("String");
			}

		}
		return fields;

	}
}
